package com.liuh.dao.impl;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.liuh.dao.BaseDao;
import com.liuh.dao.StuInfoDao;
import com.liuh.pojo.StuInfo;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;

public class StuInfoDaoImpl extends BaseDao implements StuInfoDao {

	@Deprecated
	public List<StuInfo> selectStu(StuInfo stu) {
		// TODO Auto-generated method stub
		String sql = "select * from student_info where stu_name=?";
		List<Map<String, Object>> list = executeQuery(sql, stu.getStuName());
		List<StuInfo> stus = CollUtil.newArrayList();
		for (int i = 0; i < list.size(); i++) {
			StuInfo accInfo = new StuInfo();
			BeanUtil.fillBeanWithMap(list.get(i), accInfo, true, false);
			stus.add(accInfo);
		}
		return stus;
	}

	@Override
	public List<StuInfo> selectStu(StuInfo stu, int pageIndex, int pageSize, Map<String, String> map) {
		// TODO Auto-generated method stub
		StringBuffer sbBuffer = new StringBuffer("select a1.*,a2.classes_name from student_info a1 ");
		sbBuffer.append("left join classes_info a2 on a1.classes_id=a2.id ");
		sbBuffer.append("where 1=1 ");
		List<Object> params = CollUtil.newArrayList();
		if (StrUtil.isNotBlank(stu.getStuName())) {
			sbBuffer.append("and stu_name=? ");
			params.add(stu.getStuName());
		}
		if (stu.getClassesId() != 0) {
			sbBuffer.append("and classes_id=? ");
			params.add(stu.getClassesId());
		}
		if (stu.getId() != 0) {
			sbBuffer.append("and a1.id=? ");
			params.add(stu.getId());
		}
		//
		wrapTimeRange_sql(map.get("start"), map.get("end"), sbBuffer, params, "stu_birth_day");
		sbBuffer.append("limit ?,?");
		params.add((pageIndex - 1) * pageSize);
		params.add(pageSize);
		List<Map<String, Object>> list = executeQuery(sbBuffer.toString(), params.toArray());
		List<StuInfo> stus = CollUtil.newArrayList();
		for (int i = 0; i < list.size(); i++) {
			StuInfo accInfo = new StuInfo();
			BeanUtil.fillBeanWithMap(list.get(i), accInfo, true, false);
			stus.add(accInfo);
		}
		return stus;
	}

	/**
	 * @note 使用hutool 的SqlExecutor.query(Connection conn, String sql, RsHandler<T>
	 *       rsh, Object... params)
	 * @author liuh
	 * @date 2021年5月7日 上午11:18:54
	 */
	public List<StuInfo> selectStu2(StuInfo stu, int pageIndex, int pageSize, Map<String, String> map) {
		// TODO Auto-generated method stub
		StringBuffer sbBuffer = new StringBuffer("select a1.*,a2.classes_name from student_info a1 ");
		sbBuffer.append("left join classes_info a2 on a1.classes_id=a2.id ");
		sbBuffer.append("where 1=1 ");
		List<Object> params = CollUtil.newArrayList();
		if (StrUtil.isNotBlank(stu.getStuName())) {
			sbBuffer.append("and stu_name=? ");
			params.add(stu.getStuName());
		}
		if (stu.getClassesId() != 0) {
			sbBuffer.append("and classes_id=? ");
			params.add(stu.getClassesId());
		}
		if (stu.getId() != 0) {
			sbBuffer.append("and a1.id=? ");
			params.add(stu.getId());
		}
		//
		wrapTimeRange_sql(map.get("start"), map.get("end"), sbBuffer, params, "stu_birth_day");
		sbBuffer.append("limit ?,?");
		params.add((pageIndex - 1) * pageSize);
		params.add(pageSize);
		//
		return executeQuery(sbBuffer.toString(), StuInfo.class, params.toArray());
	}

	@Override
	public int getDataCount(StuInfo stu, Map<String, String> map) {
		// TODO Auto-generated method stub
		StringBuffer sbBuffer = new StringBuffer("select count(1) 'c1' from student_info ");
		sbBuffer.append("where 1=1 ");
		List<Object> params = CollUtil.newArrayList();
		if (StrUtil.isNotBlank(stu.getStuName())) {
			sbBuffer.append("and stu_name=? ");
			params.add(stu.getStuName());
		}
		if (stu.getClassesId() != 0) {
			sbBuffer.append("and classes_id=? ");
			params.add(stu.getClassesId());
		}
		wrapTimeRange_sql(map.get("start"), map.get("end"), sbBuffer, params, "stu_birth_day");
		List<Map<String, Object>> list = executeQuery(sbBuffer.toString(), params.toArray());
		return Convert.toInt(list.get(0).get("c1"));
	}

	/**
	 * @note 根据日期查询
	 * @author liuh
	 * @date 2021年4月19日 上午10:07:04
	 */
	public static void wrapTimeRange_sql(String stat, String end, StringBuffer sbBuffer, List<Object> paras,
			String... operField) {
		String filed = operField.length > 0 ? operField[0] : "oper_time";
		if (StrUtil.isNotBlank(stat)) {
			sbBuffer.append(" and " + filed + ">= ? ");
			paras.add(stat);
		}
		if (StrUtil.isNotBlank(end)) {
			sbBuffer.append(" and " + filed + "<= ? ");
			paras.add(end);
		}
	}

	@Override
	public int addStu(StuInfo stu) {
		// TODO Auto-generated method stub
		String sql = "insert into student_info(stu_name,stu_gender,stu_age,stu_birth_day,classes_id,hobby) values(?,?,?,?,?,?)";
		return executeUpdate(sql, stu.getStuName(), stu.getStuGender(), stu.getStuAge(), stu.getStuBirthDay(),
				stu.getClassesId(), stu.getHobby());
	}

	/**
	 * @note 使用hutool
	 * @author liuh
	 * @date 2021年5月7日 上午11:33:38
	 */
	public int addStu2(StuInfo stu) {
		String sql = "insert into student_info(stu_name,stu_gender,stu_age,stu_birth_day,classes_id,hobby) values(@stuName,@stuGender,@stuAge,@stuBirthDay,@classesId,@hobby)";
		return executeUpdate(sql, stu);
	}
	
	/**
	 * @note 使用hutool Db类进行数据保存
	 * @author liuh
	 * @date 2021年5月7日 上午11:59:33
	 */
	public int addStu3(StuInfo stu) {
		int result = 0;
		try {
			result = Db.use().insert(Entity.create("student_info").parseBean(stu, true, true));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int delStu(int id) {
		// TODO Auto-generated method stub
		String sql = "delete from student_info where id=?";
		return executeUpdate(sql, id);
	}

	@Override
	public int delAllStu(Object[] ids) {
		// TODO Auto-generated method stub
		String sql = "delete from student_info where id in(" + StrUtil.repeatAndJoin("?", ids.length, ",") + ")";
		return executeUpdate(sql, ids);
	}

	@Override
	public int updStu(StuInfo stu) {
		// TODO Auto-generated method stub
		String sql = "update student_info set stu_name=?,stu_gender=?,stu_age=?,classes_id=?,stu_birth_day=?,hobby=? where id=? ";
		return executeUpdate(sql, stu.getStuName(), stu.getStuGender(), stu.getStuAge(), stu.getClassesId(),
				stu.getStuBirthDay(), stu.getHobby(), stu.getId());
	}

}
